﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using POSPlus.DatabaseOperations;
using POSPlus.EZBarTender.DatabaseOperations;
using POSPlus.QueryCreator;
using POSPlus.QueuedBackgroundWorker;

namespace POSPlus.EZBarTender
{
    public partial class ProductsTable : Form
    {
        private BatchPrinter _instanceRef;
		private DataTable _top;
		private DataTable _bottom;
		private BindingList<String> _innerOptions;
		private BindingList<String> _secondaryInnerOptions;
        private BackgroundableDataGridView _topView;
        private BackgroundableDataGridView _bottomview;

		private BackgroundWorker _printManagerImport;

		public String ConnectionString { get; private set; }

        public ProductsTable()
        {
            InitializeComponent();
        }

        public ProductsTable(BatchPrinter parent, int x, int y, int width, int height)
        {
            Left = x;
            Top = y;
            Width = width;
            Height = height;
            WindowState = parent.WindowState;
            InitializeComponent();
            _instanceRef = parent;
            initializeGridViews();

			_printManagerImport = new BackgroundWorker();
			_printManagerImport.DoWork += new DoWorkEventHandler(_printManagerImport_DoWork);
			_printManagerImport.RunWorkerCompleted += new RunWorkerCompletedEventHandler(_printManagerImport_RunWorkerCompleted);
			ConnectionString = parent.ConnectionString;

			_top = new DataTable();
			_bottom = new DataTable();
			_topView.DataSource = _top;
			_bottomview.DataSource = _bottom;

			_innerOptions = new BindingList<string>();
			_innerSelectionComboBox.DataSource = _innerOptions;
        }

        private void ProductsTable_Load(object sender, EventArgs e)
        {
            _instanceRef.Visible = false;
        }

        private void ProductsTable_FormClosing(object sender, FormClosingEventArgs e)
        {
            _instanceRef.Close();
        }

        private void ProductsTable_Resize(object sender, EventArgs e)
        {
            _topView.Size = new Size(_viewPanel.Width - 22, _btnAdd.Location.Y - 8);
            _topPictureBox.Location = new Point((_viewPanel.Width / 2) - (32 / 2),
                (_topView.Location.Y + (_topView.Height / 2)) - (32 / 2));
            _bottomview.Location = new Point(11, _btnAdd.Location.Y + _btnAdd.Height + 4);
            _bottomview.Size = 
                new Size(_viewPanel.Width - 22, (_viewPanel.Height - 8) - (_btnAdd.Location.Y + _btnAdd.Height)); 
            _bottomPictureBox.Location = new Point((_viewPanel.Width / 2) - (_bottomPictureBox.Width / 2),
                 (_bottomview.Location.Y + (_bottomview.Height / 2)) - (_bottomPictureBox.Height / 2));
        }

        private void initializeGridViews()
        {
            _topView = new BackgroundableDataGridView();
            _topView.Location = new Point(11,4);
            _topView.Size = new Size(_viewPanel.Width - 22, _btnAdd.Location.Y - 8);
            _viewPanel.Controls.Add(_topView);

            _topPictureBox.Location = new Point((_viewPanel.Width / 2) - (_topPictureBox.Width / 2), 
                (_topView.Location.Y + (_topView.Height / 2)) - (_topPictureBox.Height / 2));
            _bottomview = new BackgroundableDataGridView();
            _bottomview.Location = new Point(11, _btnAdd.Location.Y + _btnAdd.Height + 4);
            _bottomview.Size = 
                new Size(_viewPanel.Width - 22, (_viewPanel.Height - 8) - (_btnAdd.Location.Y + _btnAdd.Height));
            _bottomPictureBox.Location = new Point((_viewPanel.Width / 2) - (_bottomPictureBox.Width / 2),
                (_bottomview.Location.Y + (_bottomview.Height / 2)) - (_bottomPictureBox.Height / 2));
            _viewPanel.Controls.Add(_bottomview);

			_topView.RowsAdded += new DataGridViewRowsAddedEventHandler(_topView_RowsAdded);
			_topView.RowsRemoved += new DataGridViewRowsRemovedEventHandler(_topView_RowsRemoved);
			_bottomview.RowsAdded += new DataGridViewRowsAddedEventHandler(_bottomview_RowsAdded);
			_bottomview.RowsRemoved += new DataGridViewRowsRemovedEventHandler(_bottomview_RowsRemoved);

        }

		void _bottomview_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
		{
			if (_bottomview.Rows.Count > 0)
			{
				_btnRemove.Enabled = true;
				_btnRemoveAll.Enabled = true;
				_btnAddItems.Enabled = true;
			}
			else
			{
				_btnRemoveAll.Enabled = false;
				_btnRemove.Enabled = false;
				_btnAddItems.Enabled = false;
			}
			_addCountLabel.Text = _bottomview.Rows.Count.ToString();
		}

		void _bottomview_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
		{
			if (_bottomview.Rows.Count > 0)
			{
				_btnRemove.Enabled = true;
				_btnRemoveAll.Enabled = true;
				_btnAddItems.Enabled = true;
			}
			else
			{
				_btnRemoveAll.Enabled = false;
				_btnRemove.Enabled = false;
				_btnAddItems.Enabled = false;
			}
			_addCountLabel.Text = _bottomview.Rows.Count.ToString();
		}

		void _topView_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
		{
			if (_topView.Rows.Count < 1)
				_btnAdd.Enabled = false;
			else
				_btnAdd.Enabled = true;

			_itemCountLabel.Text = _topView.Rows.Count.ToString();
		}

		void _topView_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
		{
			if (_topView.Rows.Count > 0)
				_btnAdd.Enabled = true;
			else
				_btnAdd.Enabled = false;

			_itemCountLabel.Text = _topView.Rows.Count.ToString();
		}

        private void _btnBatchPrinter_Click(object sender, EventArgs e)
        {
            _instanceRef.Location = Location;
            _instanceRef.Size = Size;
            _instanceRef.Visible = true;
            Visible = false;
        }

        private void _backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {

            string[] arguments = (string[])e.Argument;
            string connectionString = _instanceRef.ConnectionString;

			using (SqlConnection sqlConnection = new SqlConnection(connectionString))
			{
				using (SqlDataAdapter adapter = new SqlDataAdapter(arguments[0], sqlConnection))
				{
					try
					{
						sqlConnection.Open();
						if (!arguments[1].Equals("UPC Search") && !arguments[1].Equals("Print"))
						{
							ClearDataTable(_top);
						}

						adapter.Fill(_top);
						RemoveDuplicates(_top);
						e.Result = arguments[1];
					}
					catch (SqlException ex)
					{
						MessageBox.Show(ex.Message);
						e.Result = "Fail";
					}
					finally
					{
						sqlConnection.Close();
					}
				}
			}

        }

        private void _backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {

			try
			{
				string result = (string)e.Result;
				if (result.Equals("Search"))
				{
					BindDataSource(_topView, _top);
				}
				else if (result.Equals("UPC Search"))
				{
					BindDataSource(_topView, _top);
				}
				_bottomPictureBox.Visible = false;
				_topPictureBox.Visible = false;
			}
			catch
			{
				MessageBox.Show("You would have had a lock up.", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
			}
			finally
			{
				controlsEnabled(true);
			}

        }

        #region Radio Button Controls
        private void _btnSection_CheckedChanged(object sender, EventArgs e)
        {
			if (_btnSection.Checked)
				ChangeInnerOption(GetSections(), "Section: ", false);
        }

        private void _btnVendor_CheckedChanged(object sender, EventArgs e)
        {
			if (_btnVendor.Checked)
			{
				ChangeInnerOption(GetVendors(), "Vendor: ", false);				
			}
        }

        private void _btnGroup_CheckedChanged(object sender, EventArgs e)
        {
			if (_btnGroup.Checked)
			{
				ChangeInnerOption(GetGroups(), "Group: ", false);
			}
        }

        private void _btnSubscription_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnSubscription.Checked)
            {
				ChangeInnerOption(GetAisleNumbers(), "Aisle: ", true);
			}
        }

        private void _btnDepartment_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnDepartment.Checked)
            {
				ChangeInnerOption(GetDepartmentNames(), "Departments: ", false);
            }
        }

		private void ChangeInnerOption(List<String> options, String label, bool showSecondary)
		{
			_innerOptions = new BindingList<string>(options);
			_innerSelectionComboBox.DataSource = _innerOptions;

			_selectionLabel.Text = label;

			_selectionLabel.Visible = true;
			_innerSelectionComboBox.Visible = true;

			_secondaryLabel.Visible = showSecondary;
			_secondaryInnerComboBox.Visible = showSecondary;
		}

        private void _btnImport_Click(object sender, EventArgs e)
        {
            DialogResult result = _openFileDialog.ShowDialog();

            if (result == System.Windows.Forms.DialogResult.OK)
            {
                _txtImportLocation.Text = _openFileDialog.FileName;

                Query query = new Query(
                    new Select(new string[] { "UPC", "Description", "Size", "Cert_Code", "LocAisle", "LocBlock", "LocSeq", "SVTregPrintTagFlag", "Hosted" }),
                    new From("POSplus_TagLoc_Products"));

                List<string> ors = new List<string>();

                StreamReader reader = new StreamReader(_txtImportLocation.Text);
                string str = "";
                while ((str = reader.ReadLine()) != null)
                    ors.Add("UPC LIKE '" + str.Split(',')[0] + "'");

                query.AddWhereExpressions("AND", ors.ToArray(), "OR");

                _topPictureBox.Visible = true;
                controlsEnabled(false);
                _backgroundWorker.RunWorkerAsync(new string[] { query.ToString(), "Search" });
            }
        }

        private void _btnY_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnY.Checked)
            {
                if (_btnDepartment.Checked)
                {
                    _btnDepartment.Checked = false;
                    _btnDepartment.PerformClick();
                }
                else if (_btnGroup.Checked)
                {
                    _btnGroup.Checked = false;
                    _btnGroup.PerformClick();
                }
                else if (_btnSection.Checked)
                {
                    _btnSection.Checked = false;
                    _btnSection.PerformClick();
                }
                else if (_btnSubscription.Checked)
                {
                    _btnSubscription.Checked = false;
                    _btnSubscription.PerformClick();
                }
                else if (_btnVendor.Checked)
                {
                    _btnVendor.Checked = false;
                    _btnVendor.PerformClick();
                }
            }
        }

        private void _btnN_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnN.Checked)
            {
                if (_btnDepartment.Checked)
                {
                    _btnDepartment.Checked = false;
                    _btnDepartment.PerformClick();
                }
                else if (_btnGroup.Checked)
                {
                    _btnGroup.Checked = false;
                    _btnGroup.PerformClick();
                }
                else if (_btnSection.Checked)
                {
                    _btnSection.Checked = false;
                    _btnSection.PerformClick();
                }
                else if (_btnSubscription.Checked)
                {
                    _btnSubscription.Checked = false;
                    _btnSubscription.PerformClick();
                }
                else if (_btnVendor.Checked)
                {
                    _btnVendor.Checked = false;
                    _btnVendor.PerformClick();
                }
            }
        }

        private void _btnBoth_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnBoth.Checked)
            {
                if (_btnDepartment.Checked)
                {
                    _btnDepartment.Checked = false;
                    _btnDepartment.PerformClick();
                }
                else if (_btnGroup.Checked)
                {
                    _btnGroup.Checked = false;
                    _btnGroup.PerformClick();
                }
                else if (_btnSection.Checked)
                {
                    _btnSection.Checked = false;
                    _btnSection.PerformClick();
                }
                else if (_btnSubscription.Checked)
                {
                    _btnSubscription.Checked = false;
                    _btnSubscription.PerformClick();
                }
                else if (_btnVendor.Checked)
                {
                    _btnVendor.Checked = false;
                    _btnVendor.PerformClick();
                }
            }
        }

        private void _btnShow_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnShow.Checked)
            {
                if (_btnDepartment.Checked)
                {
                    _btnDepartment.Checked = false;
                    _btnDepartment.PerformClick();
                }
                else if (_btnGroup.Checked)
                {
                    _btnGroup.Checked = false;
                    _btnGroup.PerformClick();
                }
                else if (_btnSection.Checked)
                {
                    _btnSection.Checked = false;
                    _btnSection.PerformClick();
                }
                else if (_btnSubscription.Checked)
                {
                    _btnSubscription.Checked = false;
                    _btnSubscription.PerformClick();
                }
                else if (_btnVendor.Checked)
                {
                    _btnVendor.Checked = false;
                    _btnVendor.PerformClick();
                }
            }
        }

        private void _btnHide_CheckedChanged(object sender, EventArgs e)
        {
            if (_btnHide.Checked)
            {
                if (_btnDepartment.Checked)
                {
                    _btnDepartment.Checked = false;
                    _btnDepartment.PerformClick();
                }
                else if (_btnGroup.Checked)
                {
                    _btnGroup.Checked = false;
                    _btnGroup.PerformClick();
                }
                else if (_btnSection.Checked)
                {
                    _btnSection.Checked = false;
                    _btnSection.PerformClick();
                }
                else if (_btnSubscription.Checked)
                {
                    _btnSubscription.Checked = false;
                    _btnSubscription.PerformClick();
                }
                else if (_btnVendor.Checked)
                {
                    _btnVendor.Checked = false;
                    _btnVendor.PerformClick();
                }
            }
        }

        private void _btnUPC_CheckedChanged(object sender, EventArgs e)
        {
            _txtSearch.Enabled = _btnUPC.Checked;
            if (_btnUPC.Checked)
            {
				ClearDataTable(_top);
				_innerSelectionComboBox.Visible = false;
				_selectionLabel.Visible = false;
				_secondaryInnerComboBox.Visible = false;
				_secondaryLabel.Visible = false;

                if (_txtSearch.Text.Length > 0)
                {
                    _btnSearch.Enabled = true;
                }
                else
                {
                    _btnSearch.Enabled = false;
                }
            }
            else
            {
                _btnSearch.Enabled = false;
            }
            
        }

        private void _btnSearch_Click(object sender, EventArgs e)
		{
            Query query = new Query(
                new Select(new string[] { "UPC", "Cert_Code AS ItemNo", "Description", "Size","LocAisle","LocBlock","LocSeq"}),
                new From("POSplus_TagLoc_Products"));

            if (_btnY.Checked)
                query.AddWhereExpression("SVTregPrintTagFlag = 'Y'", "AND");
            else if (_btnN.Checked)
                query.AddWhereExpression("SVTregPrintTagFlag = 'N'","AND");

            if (_btnHide.Checked)
                query.AddWhereExpression("Hosted = 1", "AND");

            List<string> ors = new List<string>();
            foreach (string word in _txtSearch.Text.Split(','))
            {
                ors.Add(parseSearch(word));
            }

            query.AddWhereExpressions("AND", ors.ToArray(), "OR");

            _topPictureBox.Visible = true;
            _txtSearch.Text = "";
            _txtSearch.Focus();
            controlsEnabled(false);
            _backgroundWorker.RunWorkerAsync(new string[] { query.ToString(), "UPC Search" });
        }

        #endregion

        private string parseSearch(string word)
        {
            string results = "";

            if (word.Contains("-"))
            {
                string[] split = word.Split('-');
                if (split.Length == 2)
                {
                    string first = "'" + upcExpand(split[0]) + "'";
                    string second = "'" + upcExpand(split[1]) + "'";
                    results = String.Format("UPC BETWEEN {0} AND {1}", first, second);
                }
            }
            else
            {
                results = String.Format("UPC = '{0}'", upcExpand(word));
            }

            return results;
        }

        private string upcExpand(string p)
        {
            string result = p;

            if (p.Length == 6)
            {
                string lastDigit = p.Substring(p.Length - 1);
                p.Remove(p.Length - 1);

                switch (lastDigit)
                {
                    case "0": result = p.Insert(2, "00000"); break;
                    case "1": result = p.Insert(2, "10000"); break;
                    case "2": result = p.Insert(2, "20000"); break;
                    case "3": result = p.Insert(3, "00000"); break;
                    case "4": result = p.Insert(4, "00000"); break;
                    case "5":
                    case "6":
                    case "7":
                    case "8":
                    case "9": result = p.Insert(5, "0000" + lastDigit); break;
                }
            }

            while (result.Length < 13)
                result = "0" + result;

            return result;
        }

        private void controlsEnabled(bool enable)
        {
            _btnBoth.Enabled = enable;
            _btnDepartment.Enabled = enable;
            _btnGroup.Enabled = enable;
            _btnHide.Enabled = enable;
            _btnN.Enabled = enable;
            _btnSection.Enabled = enable;
            _btnShow.Enabled = enable;
            _btnSubscription.Enabled = enable;
            _btnUPC.Enabled = enable;
            _btnVendor.Enabled = enable;
            _btnY.Enabled = enable;
            _btnImport.Enabled = enable;
        }

        private void _txtSearch_TextChanged(object sender, EventArgs e)
        {
            if (_txtSearch.Text.Length > 0)
                _btnSearch.Enabled = true;
            else
                _btnSearch.Enabled = false;
        }

		private void _addAllButton_Click(object sender, EventArgs e)
		{
			if (_bottom.Rows.Count < 1)
				_bottom = _top.Clone(); ;

			foreach (DataGridViewRow row in _topView.Rows)
			{
				DataRow dataRow = (row.DataBoundItem as DataRowView).Row;
				_bottom.ImportRow(dataRow);
			}

			RemoveDuplicates(_bottom);
			BindDataSource(_bottomview, _bottom);
		}

        private void _btnAdd_Click(object sender, EventArgs e)
        {
			if(_bottom.Rows.Count < 1)
				_bottom = _top.Clone();

			foreach (DataGridViewRow selectedRow in _topView.SelectedRows)
			{
				DataRow row = (selectedRow.DataBoundItem as DataRowView).Row;
				_bottom.ImportRow(row);
			}
			RemoveDuplicates(_bottom);

			BindDataSource(_bottomview, _bottom);
        }

        private void _btnRemoveAll_Click(object sender, EventArgs e)
        {
			ClearDataTable(_bottom);
        }

        private void _btnRemove_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in _bottomview.SelectedRows)
                _bottomview.Rows.Remove(row);
        }

		void _printManagerImport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
		{
			if (e.Result != null && e.Result.ToString().StartsWith("Unable to"))
				MessageBox.Show(this, e.Result.ToString(), "Print Manager Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			if (JobAdded != null)
				JobAdded(this, EventArgs.Empty);
			
			_btnAddItems.Text = "Add to Print Manager";
			_btnAddItems.Enabled = true;
		}

		void _printManagerImport_DoWork(object sender, DoWorkEventArgs e)
		{
			Object[] args = (Object[])e.Argument;
			List<Dictionary<String, String>> upcs = (List<Dictionary<String,String>>)args[0];
			String jobName = (String)args[1];

			foreach (Dictionary<String,String> upc in upcs)
			{
				Product p;
				string type = "18UPREG";
				if (string.IsNullOrEmpty(upc["LocAisle"]))
				{
					p = new Product(ConnectionString, upc["Upc"]);
				}
				else
				{
					p = new Product(ConnectionString, upc["Upc"], int.Parse(upc["LocAisle"]),
						int.Parse(upc["LocBlock"]), int.Parse(upc["LocSeq"]));
				}

				if (p.LabelWidth == "SM")
				{
					jobName = (string)args[1] + " Small Labels";
					type = "32UPREG";
				}
				else
				{
					jobName = (String)args[1];
					type = "18UPREG";
				}

				PrintManagerDisplay display = new PrintManagerDisplay(ConnectionString);
				display.Upc = p.Upc;
				display.LocAisle = p.LocAisle;
				display.LocBlock = p.LocBlock;
				display.LocSeq = p.LocSeq;
				display.JobName = jobName;
				display.Type = type;
				try
				{
					display.Create();
				}
				catch (Exception ex)
				{
					if (ex.Message.Contains("duplicate") || ex.Message.Contains("Duplicate"))
						e.Result = "Unable to add item to Print Manager\n\nA job with the same name already exists.";
					else
						e.Result = "Unable to add item to Print Manager\n\n" + ex.Message;
				}
			}
		}

        private void _btnAddItems_Click(object sender, EventArgs e)
        {
			_btnAddItems.Text = "Adding...";
			_btnAddItems.Enabled = false;

			List<Dictionary<String, String>> upcs = new List<Dictionary<String,String>>();
			foreach (DataGridViewRow item in _bottomview.Rows)
			{
				Dictionary<String, String> stringedItem = new Dictionary<string, string>(); 
				stringedItem.Add("Upc", item.Cells["Upc"].Value.ToString());
				stringedItem.Add("LocAisle",item.Cells["LocAisle"].Value.ToString());
				stringedItem.Add("LocBlock", item.Cells["LocBlock"].Value.ToString());
				stringedItem.Add("LocSeq", item.Cells["LocSeq"].Value.ToString());
				upcs.Add(stringedItem);
			}
			String jobName = _txtPrintJobDescription.Text;
			Object[] args = new Object[] { upcs, jobName };
			_printManagerImport.RunWorkerAsync(args);
        }

		private void ClearDataTable(DataTable table)
		{
			try
			{
				table.Clear();
			}
			catch (DataException)
			{
				MessageBox.Show(this, table.TableName + " could not be cleared.", "Table Clear Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private List<String> GetDepartmentNames()
		{
			List<String> names = new List<string>();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(DepartmentNamesCommandString, connection))
					{
						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
							{
								names.Add(reader["dept_name"].ToString() + " - " + reader["dept_no"].ToString());
							}
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}
			return names;
		}

		private List<String> GetSections()
		{
			List<String> sections = new List<string>();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(SectionNamesCommandString, connection))
					{
						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
								sections.Add(reader["section_name"].ToString().Trim() + " - " + reader["section"].ToString());
						}
					}
					connection.Close();
				}
			}
			catch
			{
				throw;
			}
			return sections;
		}

		private List<String> GetAisleNumbers()
		{
			List<String> numbers = new List<String>();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(AisleNumbersCommandString, connection))
					{
						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
								numbers.Add(reader["LocAisle"].ToString());
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}

			return numbers;
		}

		private List<String> GetBlockNumbers(int aisle)
		{
			List<String> numbers = new List<String>();
			numbers.Add("All");
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(GetBlockNumbersCommandString(aisle), connection))
					{
						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
								numbers.Add(reader["LocBlock"].ToString());
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}
			return numbers;
		}

		private List<String> GetGroups()
		{
			List<String> groups = new List<string>();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(GroupNamesCommandString, connection))
					{
						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
								groups.Add(reader["PGCode"].ToString());
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}
			return groups;
		}

		private List<String> GetVendors()
		{
			List<String> vendors = new List<string>();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(VendorNamesCommandString, connection))
					{
						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
								vendors.Add(reader["Vendor_No"].ToString());
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}

			return vendors;
		}


		private DataTable GetItemsFromDepartment(String departmentName)
		{
			DataTable result = new DataTable();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					string sql = GetItemsFromDepartmentCommandString(departmentName);
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						using (SqlDataAdapter adapter = new SqlDataAdapter(command))
						{
							adapter.Fill(result);
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}
			return result;
		}

		private DataTable GetItemsFromSection(String section)
		{
			DataTable result = new DataTable();
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					string sql = GetItemsFromSectionCommandString(section);
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						using (SqlDataAdapter adapter = new SqlDataAdapter(command))
						{
							adapter.Fill(result);
						}
					}
					connection.Close();
				}
			}
			catch
			{
				throw;
			}
			return result;
		}

		private DataTable GetItemsFromVendor(String vendor)
		{
			DataTable result = new DataTable();
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(GetItemsFromVendorCommandString(vendor), connection))
					{
						using (SqlDataAdapter adapter = new SqlDataAdapter(command))
						{
							adapter.Fill(result);
						}
					}
					connection.Close();
				}
			}
			catch
			{
				throw;
			}
			return result;
		}

		private DataTable GetItemsFromGroup(String groupName)
		{
			DataTable result = new DataTable();
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();

			if (ptf.Length > 0)
				ptf = " WHERE " + ptf.Substring(4);

			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					string sql = GetItemsFromGroupCommandString(groupName);

					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						using (SqlDataAdapter adapter = new SqlDataAdapter(command))
						{
							adapter.Fill(result);
						}
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}
			return result;
		}

		private DataTable GetItemsFromTagSubscription(String aisle, String block)
		{
			DataTable result = new DataTable();
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();
			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					string sql = "SELECT Upc, Cert_Code, Description, Size, LocAisle, LocBlock, LocSeq FROM POSplus_TagLoc_Products WHERE LocAisle = " + 
						aisle + " AND LocBlock = " + block + ptf + h + " ORDER BY Upc";
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						using (SqlDataAdapter adapter = new SqlDataAdapter(command))
							adapter.Fill(result);
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}

			return result;
		}

		private DataTable GetItemsFromTagSubscription(String aisle)
		{
			DataTable result = new DataTable();
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();

			try
			{
				using (SqlConnection connection = new SqlConnection(ConnectionString))
				{
					connection.Open();
					string sql = "SELECT Upc, Cert_Code, Description, Size, LocAisle, Locblock, LocSeq FROM POSplus_TagLoc_Products WHERE LocAisle = " 
						+ aisle + ptf + h + " ORDER BY Upc";
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						using (SqlDataAdapter adapter = new SqlDataAdapter(command))
							adapter.Fill(result);
					}
					connection.Close();
				}
			}
			catch (Exception)
			{
				throw;
			}
			return result;
		}

		private DataTable GetTopSubscription()
		{
			DataTable newTop = new DataTable();

			if (_btnSubscription.Checked)
			{
				if (_secondaryInnerComboBox.Text == "All")
					newTop = GetItemsFromTagSubscription(_innerSelectionComboBox.Text);
				else
					newTop = GetItemsFromTagSubscription(_innerSelectionComboBox.Text, _secondaryInnerComboBox.Text);
			}

			return newTop;
		}

		public delegate void JobAddedEventHandler(Object sender, EventArgs e);
		public event JobAddedEventHandler JobAdded;

		private void _secondaryInnerComboBox_SelectedIndexChanged(object sender, EventArgs e)
		{
			DataTable newTop = GetTopSubscription();
			_topView.Focus();
			ClearDataTable(_top);
			_top = newTop;
			BindDataSource(_topView, _top);
		}
		
		private void _innerSelectionComboBox_SelectedIndexChanged(object sender, EventArgs e)
		{
			DataTable newTop = new DataTable();

			if (_btnSubscription.Checked)
			{
				_secondaryLabel.Text = "Block: ";
				_secondaryInnerOptions = new BindingList<string>(GetBlockNumbers(int.Parse(_innerSelectionComboBox.Text)));
				_secondaryInnerComboBox.DataSource = _secondaryInnerOptions;

				_secondaryLabel.Visible = true;
				_secondaryInnerComboBox.Visible = true;

				newTop = GetTopSubscription();
			}
			else if (_btnDepartment.Checked)
			{
				newTop = GetItemsFromDepartment(_innerSelectionComboBox.Text.Split('-')[0].Trim());
			}
			else if (_btnGroup.Checked)
			{
				newTop = GetItemsFromGroup(_innerSelectionComboBox.Text);
			}
			else if (_btnVendor.Checked)
			{
				newTop = GetItemsFromVendor(_innerSelectionComboBox.Text);
			}
			else if (_btnSection.Checked)
			{
				newTop = GetItemsFromSection(_innerSelectionComboBox.Text);
			}

			_topView.Focus();
			ClearDataTable(_top);
			_top = newTop;
			BindDataSource(_topView, _top);
		}

		private void RemoveDuplicates(DataTable table)
		{
			for (int i = 0; i < table.Rows.Count; i++)
			{
				for (int j = i + 1; j < table.Rows.Count; j++)
				{
					DataRow one = table.Rows[i];
					DataRow two = table.Rows[j];
					if (DataRowEqual(one, two))
					{
						table.Rows[j].Delete();
						table.AcceptChanges();
					}
				}
			}
		}

		private bool DataRowEqual(DataRow one, DataRow two)
		{
			if (one.ItemArray.Length != two.ItemArray.Length)
				return false;

			for (int i = 0; i < one.ItemArray.Length; i++)
			{
				if (!one[i].Equals(two[i]))
					return false;
			}

			return true;
		}

		private String GetPrintTagFlagString()
		{
			if (_btnY.Checked)
				return " AND SVTregPrintTagFlag = 'Y' ";
			else if (_btnN.Checked)
				return " AND SVTregPrintTagFlag = 'N' ";
			return "";
		}

		private String GetHostedString()
		{
			if (_btnShow.Checked)
				return "";
			else
				return " AND Hosted = 1 ";
		}

		private void BindDataSource(DataGridView view, DataTable table)
		{
			BindingSource source = new BindingSource();
			source.DataSource = table;
			view.DataSource = source;
		}

		protected String DepartmentNamesCommandString
		{
			get
			{
				return "SELECT dept_name, dept_no FROM Departments INNER JOIN Products ON " +
					"Departments.dept_no = Products.department GROUP BY dept_name, dept_no ORDER BY dept_name";
			}
		}

		protected String SectionNamesCommandString
		{
			get
			{
				return "SELECT Sections.section, section_name FROM Sections INNER JOIN Products ON " +
				"Sections.section = Products.section GROUP BY Sections.section, section_name ORDER BY Sections.section_name";
			}
		}

		protected String AisleNumbersCommandString
		{
			get
			{
				return "SELECT LocAisle FROM TagSubscription GROUP BY LocAisle ORDER BY LocAisle";
			}
		}

		protected String GetBlockNumbersCommandString(int aisle)
		{
			return "SELECT LocBlock FROM TagSubScription WHERE LocAisle = "
				+ aisle.ToString() + " GROUP BY LocBlock ORDER BY LocBlock";
		}

		protected String GroupNamesCommandString
		{
			get
			{
				return "SELECT ProductGroupHeader.PGCode FROM ProductGroupHeader INNER JOIN " +
					"ProductGroupDetail ON ProductGroupHeader.PGCode = ProductGroupDetail.PGCode " +
					"GROUP BY ProductGroupHeader.PGCode";
			}
		}

		protected String VendorNamesCommandString
		{
			get
			{
				return "SELECT Vendor_No FROM Vendors INNER JOIN Products ON Vendors.Vendor_No = " +
					"Products.Vendor GROUP BY Vendor_No ORDER BY Vendor_No";
			}
		}

		protected String GetItemsFromDepartmentCommandString(String departmentName)
		{
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();
			if (ptf.Length > 0)
				ptf = " WHERE" + ptf.Substring(4);

			return "SELECT prods.Upc, prods.Cert_Code, prods.Description, prods.Size, prods.LocAisle, " +
				"prods.LocBlock, prods.LocSeq FROM (SELECT * FROM POSplus_TagLoc_Products " + ptf + h + 
				") prods INNER JOIN (SELECT dept_no FROM Departments WHERE dept_name = '" + 
				departmentName + "') dept " + "ON prods.department = dept.dept_no ORDER BY prods.Upc";
		}

		protected String GetItemsFromSectionCommandString(String section)
		{
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();

			return "SELECT Upc, Cert_Code, Description, Size, LocAisle, LocBlock, LocSeq FROM " +
				"POSplus_TagLoc_Products WHERE Section = " + section.Split('-')[1].Trim() + ptf + 
				h + " ORDER BY Upc";
		}

		protected String GetItemsFromVendorCommandString(String vendor)
		{
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();

			return "SELECT Upc, Cert_Code, Description, Size, LocAisle, LocBlock, LocSeq FROM " +
				"POSplus_TagLoc_Products WHERE Vendor = " + vendor + ptf + h + " ORDER BY Upc";
		}

		protected String GetItemsFromGroupCommandString(String groupName)
		{
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();

			if (ptf.Length > 0)
				ptf = " WHERE " + ptf.Substring(4);

			return "SELECT prods.Upc, Cert_Code, Description, Size, LocAisle, LocSeq " +
				"FROM (SELECT * FROM POSplus_TagLoc_Products " + ptf + h + ") prods INNER JOIN " +
				"(SELECT upc FROM ProductGroupDetail WHERE PGCode = '" + groupName + 
				"') groups ON prods.Upc = groups.upc";
		}

		protected String GetItemsFromTagSubscriptionCommandString(String aisle, String block)
		{
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();
			return "SELECT Upc, Cert_Code, Description, Size, LocAisle, LocBlock, LocSeq FROM " +
				"POSplus_TagLoc_Products WHERE LocAisle = " + aisle + " AND LocBlock = " + 
				block + ptf + h + " ORDER BY Upc";
		}

		protected String GetItemsFromTagSubscriptionCommandString(String aisle)
		{
			String ptf = GetPrintTagFlagString();
			String h = GetHostedString();

			return "SELECT Upc, Cert_Code, Description, Size, LocAisle, LocBlock, LocSeq FROM " + 
				"POSplus_TagLoc_Products WHERE LocAisle = "	+ aisle + ptf + h + " ORDER BY Upc";
		}

        private void AdjustWidthComboBox_DropDown(object sender, EventArgs e)
        {
            ComboBox senderComboBox = (ComboBox)sender;
            int width = senderComboBox.DropDownWidth;
            Graphics g = senderComboBox.CreateGraphics();
            Font font = senderComboBox.Font;
            int vertScrollBarWidth =
                (senderComboBox.Items.Count > senderComboBox.MaxDropDownItems)
                ? SystemInformation.VerticalScrollBarWidth : 0;

            int newWidth;
            foreach (string s in ((ComboBox)sender).Items)
            {
                newWidth = (int)g.MeasureString(s, font).Width
                    + vertScrollBarWidth;
                if (width < newWidth)
                {
                    width = newWidth;
                }
            }
            senderComboBox.DropDownWidth = width;
        }
    }
}